﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.ETL
{
    public partial class GrabDataEditor : Form, IComponentEditor 
    {
        private object _ownerActivityNode;
        private GrabDataComponent _GrabData;

        /// <summary>
        /// 
        /// </summary>
        public GrabDataEditor()
        {
            InitializeComponent();
        }

        private void GrabDataEditor_Load(object sender, EventArgs e)
        {
            this.cbo_KeyDataProvider.Items.Clear();
            this.cbo_SrcDataProvider.Items.Clear();
            this.cbo_TgtDataProvider.Items.Clear();
            for(int i=0;i<=1;i++)
            {
                DataProviderType dbProvider = (DataProviderType) i;
                this.cbo_KeyDataProvider.Items.Add(dbProvider.ToString());
                this.cbo_SrcDataProvider.Items.Add(dbProvider.ToString());
                this.cbo_TgtDataProvider.Items.Add(dbProvider.ToString());
            }

            //foreach(DataGridViewRow dr in this.dv_FieldMappings.Rows)
            //{
            //    if(dr.IsNewRow)
            //    {
            //        DataGridViewComboBoxCell cellAction = (DataGridViewComboBoxCell) dr.Cells[2];
            //        cellAction.Items.Clear();
            //        for (int i = 0; i <= 4; i++)
            //        {
            //            FieldDataPopulationAction action = (FieldDataPopulationAction)i;
            //            cellAction.Items.Add(action.ToString());
            //        }
            //    }
            //}
        }

        public event UpdateComponentSettings UpdateComponentSettingEvent;

        public object OwnerActivityNode
        {
            get { return _ownerActivityNode; }
            set { _ownerActivityNode = value; }
        }

        public Type WorkflowComponentType
        {
            get { return typeof(GrabDataComponent); }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="globalVars"></param>
        /// <param name="fromComponents"></param>
        /// <param name="activityNode"></param>
        /// <param name="wfComponent"></param>
        public void LoadSettings(GlobalVariable[] globalVars, WorkflowComponentBase[] fromComponents,
                                 object activityNode, WorkflowComponentBase wfComponent)
        {
            this._ownerActivityNode = activityNode;
            this._GrabData = (GrabDataComponent) wfComponent;

            this.cbo_KeyDataProvider.SelectedIndex = (int) this._GrabData.KeyDataProvider;
            this.txt_KeyConn.Text = this._GrabData.KeyConnStr;
            this.rt_GetKeySql.Text = this._GrabData.KeySql;

            this.cbo_SrcDataProvider.SelectedIndex = (int) this._GrabData.SrcDataProvider;
            this.txt_SrcConn.Text = this._GrabData.SrcConnStr;
            this.rt_DataSql.Text = this._GrabData.SrcDataSql;

            this.cbo_TgtDataProvider.SelectedIndex = (int) this._GrabData.TgtDataProvider;
            this.txt_TgtConn.Text = this._GrabData.TgtConnStr;
            if(this.cbo_TgtTable.Items.Count>0)
            {
                for(int i=0;i<this.cbo_TgtTable.Items.Count;i++)
                {
                    if(this.cbo_TgtTable.Items[i].ToString()==this._GrabData.TgtTableName)
                    {
                        this.cbo_TgtTable.SelectedIndex = i;
                        break;
                    }
                }
            }
            else
            {
                this.cbo_TgtTable.Text = this._GrabData.TgtTableName;
            }
            this.cbo_SkipWhenFieldNotNull.Text = this._GrabData.NonNullFieldSkipUpdate;
            this.txt_LogFile.Text = this._GrabData.LogFilePath;
            this.txt_ErrorFile.Text = this._GrabData.ErrorFilePath;
            this.ck_UseBcp.Checked = this._GrabData.UseBcp;

            this.PopulateFieldMappings();

            if(this._GrabData.FieldMappings !=null && this._GrabData.FieldMappings.Count>0)
            {
                foreach(GrabDataFieldMapping fieldMapping in this._GrabData.FieldMappings)
                {
                    foreach(DataGridViewRow dr in this.dv_FieldMappings.Rows)
                    {
                        if(dr.Cells[0].Value !=null)
                        {
                            string tgtFieldName = dr.Cells[0].Value.ToString();
                            if(tgtFieldName.ToLower()==fieldMapping.TgtField.ToLower())
                            {
                                DataGridViewComboBoxCell cellFrom = (DataGridViewComboBoxCell) dr.Cells[1];
                                cellFrom.Value = fieldMapping.SrcField;
                                DataGridViewComboBoxCell cellAction = (DataGridViewComboBoxCell) dr.Cells[2];
                                cellAction.Value = fieldMapping.Action.ToString();
                                break;
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public void SaveSettings()
        {
            this._GrabData.KeyDataProvider = (DataProviderType) this.cbo_KeyDataProvider.SelectedIndex;
            this._GrabData.KeyConnStr = this.txt_KeyConn.Text;
            this._GrabData.KeySql = this.rt_GetKeySql.Text;

            this._GrabData.SrcDataProvider = (DataProviderType) this.cbo_SrcDataProvider.SelectedIndex;
            this._GrabData.SrcConnStr = this.txt_SrcConn.Text;
            this._GrabData.SrcDataSql = this.rt_DataSql.Text;

            this._GrabData.TgtDataProvider = (DataProviderType) this.cbo_TgtDataProvider.SelectedIndex;
            this._GrabData.TgtConnStr = this.txt_TgtConn.Text;
            this._GrabData.TgtTableName = this.cbo_TgtTable.Text;
            this._GrabData.NonNullFieldSkipUpdate = this.cbo_SkipWhenFieldNotNull.Text;
            if(!this.ck_Skip.Checked)
            {
                this._GrabData.NonNullFieldSkipUpdate = string.Empty;
            }
            this._GrabData.LogFilePath = this.txt_LogFile.Text;
            this._GrabData.ErrorFilePath = this.txt_ErrorFile.Text;
            this._GrabData.UseBcp = this.ck_UseBcp.Checked;

            this._GrabData.FieldMappings=new List<GrabDataFieldMapping>();
            foreach(DataGridViewRow dr in this.dv_FieldMappings.Rows)
            {
                if(!dr.IsNewRow && dr.Cells[0].Value !=null && dr.Cells[1].Value !=null)
                {
                    string srcFieldName = dr.Cells[1].Value.ToString();
                    string tgtFieldName = dr.Cells[0].Value.ToString();
                    if (!string.IsNullOrEmpty(srcFieldName))
                    {
                        FieldDataPopulationAction action = FieldDataPopulationAction.AlwaysWhenEmpty;
                        DataGridViewComboBoxCell cellAction = (DataGridViewComboBoxCell) dr.Cells[2];
                        if (cellAction.Value != null)
                        {
                            action =
                                (FieldDataPopulationAction)
                                Enum.Parse(typeof (FieldDataPopulationAction), cellAction.Value.ToString());
                        }
                        GrabDataFieldMapping fieldMapping = new GrabDataFieldMapping();
                        fieldMapping.SrcField = srcFieldName;
                        fieldMapping.TgtField = tgtFieldName;
                        fieldMapping.Action = action;
                        this._GrabData.FieldMappings.Add(fieldMapping);
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public TestResult Test()
        {
            this.SaveSettings();
            GrabDataInstruction instruction =
                new GrabDataInstruction(this._GrabData.ComponentID, Guid.Empty, this._GrabData.KeyDataProvider,
                                        this._GrabData.KeyConnStr, this._GrabData.KeySql, this._GrabData.SrcDataProvider,
                                        this._GrabData.SrcConnStr, this._GrabData.SrcDataSql,
                                        this._GrabData.TgtDataProvider, this._GrabData.TgtConnStr,
                                        this._GrabData.TgtTableName, this._GrabData.NonNullFieldSkipUpdate, 
                                        this._GrabData.FieldMappings, this._GrabData.UseBcp,
                                        this._GrabData.LogFilePath, this._GrabData.ErrorFilePath);
            GrabDataWorker worker=new GrabDataWorker();
            worker.SetInstruction(Guid.Empty,instruction, string.Empty, this._GrabData.ComponentName);
            return worker.Test();
        }

        //private void dv_FieldMappings_CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        //{
        //    foreach (DataGridViewRow dr in this.dv_FieldMappings.Rows)
        //    {
        //        if (dr.IsNewRow)
        //        {
        //            DataGridViewComboBoxCell cellAction = (DataGridViewComboBoxCell)dr.Cells[2];
        //            cellAction.Items.Clear();
        //            for (int i = 0; i <= 4; i++)
        //            {
        //                FieldDataPopulationAction action = (FieldDataPopulationAction)i;
        //                cellAction.Items.Add(action.ToString());
        //            }
        //        }
        //    }
        //}

        private void btn_Test_Click(object sender, EventArgs e)
        {
            TestResult result = this.Test();
            MessageBox.Show(result.Message, result.Succeed.ToString());
        }

        private void btn_Cancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_OK_Click(object sender, EventArgs e)
        {
            this.SaveSettings();
            if(this.UpdateComponentSettingEvent !=null)
            {
                this.UpdateComponentSettingEvent(this._ownerActivityNode, this._GrabData);
            }
            this.Close();
        }

        private void btn_RefresnTargetTables_Click(object sender, EventArgs e)
        {
            string connStr = this.txt_TgtConn.Text;
            DataProviderType tgtDbProvider = (DataProviderType)this.cbo_TgtDataProvider.SelectedIndex;
            string[] tblNames = DbUtil.GetTableNames(tgtDbProvider, string.Empty, connStr);
            this.cbo_TgtTable.Items.Clear();
            foreach (string tblName in tblNames)
            {
                this.cbo_TgtTable.Items.Add(tblName);
            }
        }

        private void cbo_TgtTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.btn_Populate_Click(sender,e);
        }

        private void btn_Populate_Click(object sender, EventArgs e)
        {
            this.PopulateFieldMappings();
        }

        private void PopulateFieldMappings()
        {
            this.dv_FieldMappings.Rows.Clear();
            if (string.IsNullOrEmpty(this.rt_GetKeySql.Text))
            {
                MessageBox.Show("You need to enter select key sql first");
                return;
            }

            string getkeySql = string.Format("select top 1 * from ({0}) tmp", this.rt_GetKeySql.Text);
            DataTable dtKey = DbUtil.GetTableDataBySQL(this.txt_KeyConn.Text, "keys", getkeySql);
            if (dtKey == null || dtKey.Rows.Count == 0)
            {
                MessageBox.Show("Unable to get key");
                return;
            }
            if (dtKey.Rows[0][0] == null)
            {
                MessageBox.Show("Unable to get key");
                return;
            }
            string keyValue = dtKey.Rows[0][0].ToString();
            DataProviderType srcDbProvider = (DataProviderType)this.cbo_SrcDataProvider.SelectedIndex;
            DataProviderType tgtDbProvider = (DataProviderType)this.cbo_TgtDataProvider.SelectedIndex;
            string srcSql = this.rt_DataSql.Text;
            srcSql = srcSql.Replace("{0}", keyValue);
            //Regex keyFilterRegex = new Regex("(\\S+)\\s*=\\s*\\{0\\}");
            //Match keyFilterMatch = keyFilterRegex.Match(srcSql);
            //if (keyFilterMatch.Success)
            //{
            //    srcSql = srcSql.Replace("{0}", keyValue);
            //}
            //keyFilterRegex = new Regex("(\\S+)\\s*in\\s*\\(\\{0\\}\\)");
            //keyFilterMatch = keyFilterRegex.Match(srcSql);
            //if (keyFilterMatch.Success)
            //{
            //    srcSql = srcSql.Replace("{0}", keyValue);
            //}
            //srcSql = srcSql.Trim();
            //if (srcSql.ToLower().EndsWith("where"))
            //{
            //    srcSql = srcSql.Substring(0, srcSql.Length - 5);
            //}
            //if (srcSql.ToLower().EndsWith("and"))
            //{
            //    srcSql = srcSql.Substring(0, srcSql.Length - 3);
            //}
            TableColumn[] srcCols = DbUtil.GetTableColumns(srcDbProvider, this.txt_SrcConn.Text, srcSql);
            TableColumn[] tgtCols =
                DbUtil.GetTableColumns(tgtDbProvider, this.txt_TgtConn.Text, string.Empty, this.cbo_TgtTable.Text);
            this.cbo_SkipWhenFieldNotNull.Items.Clear();
            if(tgtCols !=null && tgtCols.Length>0)
            {
                foreach(TableColumn col in tgtCols)
                {
                    this.cbo_SkipWhenFieldNotNull.Items.Add(col.ColumnName);
                }
            }

            if (srcCols == null || srcCols.Length == 0 || tgtCols == null || tgtCols.Length == 0)
            {
                MessageBox.Show("Unable to get table schemas");
                return;
            }

            
            foreach (TableColumn tgtCol in tgtCols)
            {
                DataGridViewRow dr = new DataGridViewRow();
                DataGridViewTextBoxCell cellTo = new DataGridViewTextBoxCell();
                cellTo.Value = tgtCol.ColumnName;
                dr.Cells.Add(cellTo);

                DataGridViewComboBoxCell cellFrom = new DataGridViewComboBoxCell();
                cellFrom.Items.Add(string.Empty);
                cellFrom.Value = string.Empty;
                TableColumn srcCol = new TableColumn();
                foreach (TableColumn fromCol in srcCols)
                {
                    cellFrom.Items.Add(fromCol.ColumnName);
                    if (fromCol.ColumnName.ToLower() == tgtCol.ColumnName.ToLower())
                    {
                        srcCol = fromCol;
                        cellFrom.Value = srcCol.ColumnName;
                    }
                }
                dr.Cells.Add(cellFrom);

                DataGridViewComboBoxCell cellAction = new DataGridViewComboBoxCell();
                for (int i = 0; i <= 4; i++)
                {
                    FieldDataPopulationAction action = (FieldDataPopulationAction)i;
                    cellAction.Items.Add(action.ToString());
                }
                cellAction.Value = FieldDataPopulationAction.AlwaysWhenEmpty.ToString();
                dr.Cells.Add(cellAction);
                this.dv_FieldMappings.Rows.Add(dr);
            }
        }

        private void btn_BrowseLog_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg=new SaveFileDialog();
            if(dlg.ShowDialog()==System.Windows.Forms.DialogResult.OK)
            {
                this.txt_LogFile.Text = dlg.FileName;
            }
        }

        private void btn_BrowseError_Click(object sender, EventArgs e)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                this.txt_ErrorFile.Text = dlg.FileName;
            }
        }

        private void dv_FieldMappings_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            Console.WriteLine(
                string.Format("Row: {0} Col {1}: Error {2}", e.RowIndex, e.ColumnIndex, e.Exception.Message));
        }

        private void ck_Skip_CheckedChanged(object sender, EventArgs e)
        {
            if (this.ck_Skip.Checked)
                this.cbo_SkipWhenFieldNotNull.Enabled = true;
            else
            {
                this.cbo_SkipWhenFieldNotNull.Enabled = false;
                this.cbo_SkipWhenFieldNotNull.Text = string.Empty;
            }
        }
    }
}
